package cn.edu.sdjzu.xg.kcsj.dao;

import cn.edu.sdjzu.xg.kcsj.domain.Activity;
import cn.edu.sdjzu.xg.kcsj.exception.BysjException;
import cn.edu.sdjzu.xg.kcsj.security.User;
import cn.edu.sdjzu.xg.kcsj.security.UserService;
import cn.edu.sdjzu.xg.kcsj.util.CommonDao;
import cn.edu.sdjzu.xg.kcsj.util.Condition;
import cn.edu.sdjzu.xg.kcsj.util.JdbcHelper;
import cn.edu.sdjzu.xg.kcsj.util.Pagination;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Collection;
import java.sql.Date;
import java.util.List;
import java.util.TreeSet;

public class ActivityDao {
    private static ActivityDao activityDao = new ActivityDao();

    private ActivityDao() {
    }

    public static ActivityDao getInstance() {
        if(activityDao == null){
            activityDao = new ActivityDao();
        }
        return activityDao;
    }

    public Collection<Activity> findByTI(int id, Connection connection) throws SQLException, BysjException {
        Collection<Activity> activities = new TreeSet<>();
        String sql_select_id = "SELECT * FROM activity WHERE teacher_id = ?";
        PreparedStatement pstmt_select_id =
                connection.prepareStatement(sql_select_id);
        pstmt_select_id.setInt(1,id);
        ResultSet resultSet_select_id = pstmt_select_id.executeQuery();
        //如果表中存在id对应的记录，则获得表中的字段值，并创建对象
        while(resultSet_select_id.next()){
            int a_id = resultSet_select_id.getInt("id");
            String description = resultSet_select_id.getString("description");
            String introduction = resultSet_select_id.getString("introduction");
            int participantsNum = resultSet_select_id.getInt("participantsNum");
            Date startTime = resultSet_select_id.getDate("startTime");
            Date endTime = resultSet_select_id.getDate("endTime");
            int teacherId = resultSet_select_id.getInt("teacher_id");
            int leaderId = resultSet_select_id.getInt("leader_id");
            int state = resultSet_select_id.getInt("state");
            int signedNum = resultSet_select_id.getInt("signedNum");
            User teacher = UserService.getInstance().find(teacherId);
            User leader = UserService.getInstance().find(leaderId);
            Activity desiredActivity = new Activity(a_id, description, introduction,
                    participantsNum,startTime,endTime,teacher,leader,state,signedNum);
            activities.add(desiredActivity);
        }
        //关闭三个资源
        JdbcHelper.close(resultSet_select_id, pstmt_select_id, connection);
        return activities;
    }

    public boolean add(Activity activity) throws SQLException {
        //获取数据库连接
        Connection connection = JdbcHelper.getConn();
        //准备预编译的语句
        String sql_insert = "INSERT INTO activity " +
                "(description, introduction, participantsNum, startTime," +
                "endTime,teacher_id,leader_id,state,signedNum) VALUES (?, ?, ?, ?, ?, ?, ?, ?,?)";
        PreparedStatement pstmt_update = connection.prepareStatement(sql_insert);
        pstmt_update.setString(1, activity.getDescription());
        pstmt_update.setString(2, activity.getIntroduction());
        pstmt_update.setInt(3, activity.getParticipantsNum());
        pstmt_update.setDate(4, activity.getStartTime());
        pstmt_update.setDate(5, activity.getEndTime());
        pstmt_update.setInt(6, activity.getTeacher().getId());
        pstmt_update.setInt(7, activity.getLeader().getId());
        pstmt_update.setInt(8, activity.getState());
        pstmt_update.setInt(9, activity.getSignedNum());
        //执行预编译语句，影响的行数保存在rowAffected中
        int rowAffected = pstmt_update.executeUpdate();
        JdbcHelper.close(pstmt_update,connection);
        return rowAffected > 0;
    }

    public Activity find(int id) throws SQLException, BysjException {
        //创建Activity类型的引用，暂不创建对象
        Activity desiredActivity = null;
        //获得数据库连接
        Connection connection = JdbcHelper.getConn();
        String sql_select_id = "SELECT * FROM activity WHERE id = ?";
        PreparedStatement pstmt_select_id =
                connection.prepareStatement(sql_select_id);
        pstmt_select_id.setInt(1,id);
        ResultSet resultSet_select_id = pstmt_select_id.executeQuery();
        //如果表中存在id对应的记录，则获得表中的字段值，并创建对象
        if(resultSet_select_id.next()){
            String description = resultSet_select_id.getString("description");
            String introduction = resultSet_select_id.getString("introduction");
            int participantsNum = resultSet_select_id.getInt("participantsNum");
            Date startTime = resultSet_select_id.getDate("startTime");
            Date endTime = resultSet_select_id.getDate("endTime");
            int teacherId = resultSet_select_id.getInt("teacher_id");
            int leaderId = resultSet_select_id.getInt("leader_id");
            int state = resultSet_select_id.getInt("state");
            int signedNum = resultSet_select_id.getInt("signedNum");
            User teacher = UserService.getInstance().find(teacherId);
            User leader = UserService.getInstance().find(leaderId);
            desiredActivity = new Activity(id, description, introduction,
                    participantsNum,startTime,endTime,teacher,leader,state,signedNum);
        }
        //关闭三个资源
        JdbcHelper.close(resultSet_select_id, pstmt_select_id, connection);
        return desiredActivity;
    }
    public Collection<Activity> findAll(Pagination pagination, List<Condition> conditionList) throws SQLException, BysjException {
        //创建集合类对象，用来保存所有记录代表的Activity对象
        //TreeSet按compare(Object o)方法指定的顺序排序
        Collection<Activity> desiredActivitys = new TreeSet<Activity>();
        //获取数据库连接
        Connection connection = JdbcHelper.getConn();
        //创建查询Activity的主语句
        StringBuilder sql_select = new StringBuilder("SELECT * FROM Activity ");
        //如果有条件，则生成条件子句，并附加到主语句后面
        if(conditionList != null){
            String whereClause = Condition.toWhereClause(conditionList);
            sql_select.append(whereClause);
        }
        //获取Activity中的元组总数
        int totalNum = CommonDao.getInstance().count(connection, "Activity",conditionList);
        if(pagination != null){
            String paginationClause = pagination.toLimitClause(totalNum);
            sql_select.append(paginationClause);
        }


        PreparedStatement pstmt_select_all =
                connection.prepareStatement(sql_select.toString());
        //执行预编译语句，结果集保存在resultSet中
        ResultSet resultSet_select_all = pstmt_select_all.executeQuery();
        //遍历resultSet
        while(resultSet_select_all.next()){
            int id = resultSet_select_all.getInt("id");
            String description = resultSet_select_all.getString("description");
            String introduction = resultSet_select_all.getString("introduction");
            int participantsNum = resultSet_select_all.getInt("participantsNum");
            Date startTime = resultSet_select_all.getDate("startTime");
            Date endTime = resultSet_select_all.getDate("endTime");
            int teacherId = resultSet_select_all.getInt("teacher_id");
            int leaderId = resultSet_select_all.getInt("leader_id");
            int state = resultSet_select_all.getInt("state");
            int signedNum = resultSet_select_all.getInt("signedNum");
            User teacher = UserService.getInstance().find(teacherId);
            User leader = UserService.getInstance().find(leaderId);
            Activity desiredActivity = new Activity(id,description, introduction, participantsNum,
                    startTime,endTime,teacher,leader,state,signedNum);
            desiredActivitys.add(desiredActivity);
        }
        JdbcHelper.close(resultSet_select_all,pstmt_select_all,connection);
        return desiredActivitys;
    }
    public Collection<Activity> findAll() throws SQLException, BysjException {
        //创建集合类对象，用来保存所有记录代表的Activity对象
        //TreeSet按compare(Object o)方法指定的顺序排序
        Collection<Activity> desiredActivitys = new TreeSet<Activity>();
        //获取数据库连接
        Connection connection = JdbcHelper.getConn();
        //创建查询Activity的主语句
        StringBuilder sql_select = new StringBuilder("SELECT * FROM Activity ");
        PreparedStatement pstmt_select_all =
                connection.prepareStatement(sql_select.toString());
        //执行预编译语句，结果集保存在resultSet中
        ResultSet resultSet_select_all = pstmt_select_all.executeQuery();
        //遍历resultSet
        while(resultSet_select_all.next()){
            int id = resultSet_select_all.getInt("id");
            String description = resultSet_select_all.getString("description");
            String introduction = resultSet_select_all.getString("introduction");
            int participantsNum = resultSet_select_all.getInt("participantsNum");
            Date startTime = resultSet_select_all.getDate("startTime");
            Date endTime = resultSet_select_all.getDate("endTime");
            int teacherId = resultSet_select_all.getInt("teacher_id");
            int leaderId = resultSet_select_all.getInt("leader_id");
            int state = resultSet_select_all.getInt("state");
            int signedNum = resultSet_select_all.getInt("signedNum");
            User teacher = UserService.getInstance().find(teacherId);
            User leader = UserService.getInstance().find(leaderId);
            Activity desiredActivity = new Activity(id,description, introduction, participantsNum,
                    startTime,endTime,teacher,leader,state,signedNum);
            desiredActivitys.add(desiredActivity);
        }
        JdbcHelper.close(resultSet_select_all,pstmt_select_all,connection);
        return desiredActivitys;
    }

    public boolean update(Activity activity) throws SQLException {
        //获取数据库连接
        Connection connection = JdbcHelper.getConn();
        //准备预编译的语句
        String sql_update =
                "UPDATE activity SET description = ?, introduction = ?, " +
                        "participantsNum = ?, startTime = ?, endTime = ?," +
                        " teacher_id = ?, leader_id = ?, state = ?,signedNUm=? " +
                        "WHERE id = ?";
        PreparedStatement pstmt_update = connection.prepareStatement(sql_update);
        pstmt_update.setString(1, activity.getDescription());
        pstmt_update.setString(2, activity.getIntroduction());
        pstmt_update.setInt(3, activity.getParticipantsNum());
        pstmt_update.setDate(4, activity.getStartTime());
        pstmt_update.setDate(5, activity.getEndTime());
        pstmt_update.setInt(6, activity.getTeacher().getId());
        pstmt_update.setInt(7, activity.getLeader().getId());
        pstmt_update.setInt(8, activity.getState());
        pstmt_update.setInt(9, activity.getSignedNum());
        pstmt_update.setInt(10, activity.getId());
        //执行预编译语句，影响的行数保存在rowAffected中
        int rowAffected = pstmt_update.executeUpdate();
        JdbcHelper.close(pstmt_update,connection);
        return rowAffected > 0;
    }

    public boolean delete(int id) throws SQLException {
        //获取数据库连接
        Connection connection = JdbcHelper.getConn();
        //准备预编译的语句
        String sql_delete_id = "DELETE FROM activity WHERE id = ?";
        PreparedStatement pstmt_delete =
                connection.prepareStatement(sql_delete_id);

        pstmt_delete.setInt(1, id);

        //执行预编译语句，影响的行数保存在rowAffected中
        int rowAffected = pstmt_delete.executeUpdate();

        JdbcHelper.close(pstmt_delete,connection);
        return rowAffected > 0;
    }

    public boolean delete(Activity activity) throws SQLException {
        return this.delete(activity.getId());
    }
}
